package Messages;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import Logic.SendMail;

import ocsf.server.ConnectionToClient;
import server.SQLConnection;
/**
 * this class implements the workers return book 
 * @author Ron Shenkerman
 * @author Gabi Revah
 * @author Aviad Almog
 * @author Ivan Onyshchenko
 */
public class MessageReturnBook {

	public String lendid,subscriberid,copyid;
	Date statdaye,enddate;
	public int i,j,day,temp;
	static int row2;
	
	PreparedStatement ps,p1,p2,p3;
	private ConnectionToClient client ;
	/**
	 * constractor of this class 
	 * @param client
	 */
	public MessageReturnBook(ConnectionToClient client) {
		super();
		this.client = client;
	}
	/**
	 * return book and changing the status of book in the DB 
	 * @param str
	 */
	public void returnabook(String[] str){
		
		try 
		{

			System.out.println("lending a book");
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			stmt = conn.getConnection().createStatement();
			ps=conn.getConnection().prepareStatement("SELECT * FROM db1.lending WHERE CopyId = ? And Lend = ?;");
			ps.setString(1,str[1]);
			ps.setString(2, "yes");
			ResultSet rs = ps.executeQuery(); 
			
					try {
				if(rs.next()){	//check if book is in lending status
					java.sql.PreparedStatement ps = conn.getConnection().prepareStatement("Update DB1.lending SET ActualReturnDate=? WHERE CopyId = ? And Lend = ?");
					java.util.Date today = new java.util.Date();
					java.sql.Date timestamp = new java.sql.Date(today.getTime());
					ps.setDate(1,timestamp);						
					ps.setString(2,str[1]);
					ps.setString(3, "yes");
					ps.executeUpdate();
				java.sql.PreparedStatement ps1 = conn.getConnection().prepareStatement("Update DB1.lending SET Lend=? WHERE CopyId = ? And Lend = ?");
				ps1.setString(1,"no");
				ps1.setString(2,str[1]);
				ps1.setString(3, "yes");
				ps1.executeUpdate();
				java.sql.PreparedStatement p2 = conn.getConnection().prepareStatement("select * from db1.reservation WHERE BookID = ?");
				p2.setString(1,str[1]);
				//p2.setString(2,rs.getString(2));
				p2.executeQuery(); 
				ResultSet rs1 = p2.executeQuery(); 
				if(rs1.next()){//book is reseved sending mail to the subscriber that reserved the book
					java.sql.PreparedStatement p3 = conn.getConnection().prepareStatement("select * from db1.subscriber WHERE ID = ? ");
					p3.setString(1,rs1.getString(4));
					p3.executeQuery(); 
					ResultSet rs3 = p3.executeQuery(); 
					rs3.next();
					SendMail sm=new SendMail("obl.librarian@gmail.com",rs3.getString(2),"reservation");
						}
				
											System.out.println("book Returned");
											client.sendToClient("bookReturned");
							}
								else{
									System.out.println("book not Returned");
										client.sendToClient("booknotReturned");
									}
						  
					}
								catch (SQLException e)
									{	
									e.printStackTrace();
									}
			}
  
			catch (Exception ex )
			{
				ex.printStackTrace();
			}
		}
	
}
